﻿/***********************************************************************************************************************************
 *    Name: Diagnostics.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
	SQLServer: Buffer Manager – Page Life Expectancy
	SQLServer: Memory Manager – Memory Grants Pending
	SQLServer: Memory Manager – Target Server Memory
	SQLServer: Memory Manager – Total Server Memory
	SQLServer: SQL Statistics – Batch Requests/sec
	SQLServer: SQL Statistics – Compilations/sec
**/


SELECT
	counter_name= REPLACE(counter_name, N'(KB)', N'(GB)'),
	cntr_value	= CAST(ROUND(cntr_value/1048576.0,1) AS DECIMAL(6,1))
FROM sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)','Target Server Memory (KB)')
UNION ALL
SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page Life Expectancy' AND instance_name = N''
UNION ALL

SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = N'Memory Grants Pending'
UNION ALL

SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = N'Batch Requests/sec'
UNION ALL

SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = N'SQL Compilations/sec                                                                                                            '

